﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class EntertainmenRestaurantOrderCtrl
    {
        #region 娱乐、餐单预订信息获取

        /// <summary>
        /// 导游报销用，根据团号获取娱乐餐单信息
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static DataTable GetEntertainmentRestaurantSignBill(string tourGroupId)
        {
            ProviderTypeEnum[] types = new ProviderTypeEnum[] { ProviderTypeEnum.餐厅, ProviderTypeEnum.景点, ProviderTypeEnum.演出};

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            //根据供应商为签约供应商，和制定团号的签单娱乐信息
            formatSql.AppendFormat(@"SELECT en.[EntertainmentOrderID],en.[ExpenseID], en.[TourgroupID],en.[TourgroupName],en.[ProviderID],en.[ProviderName], 
                                                en.[AdultNum],en.[AdultUnitPrice],en.[AccompanyNum],en.[AccompanyUnitPrice],en.[LocalPlanScheduleID], en.[ServiceDate], 
                                                en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], en.[AccompanyUnitPrice],
                                                en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState], 
                                                e.[Budget],e.[Status],e.[TypeBelong] ,e.[CreateByUserName],e.[TourGroupScheduleID],e.[WipeOffMethod]   
                                                FROM fee_Expense e,fee_EntertainmentOrder en 
                                                WHERE  e.expenseid=en.expenseid AND e.[WipeOffMethod]='{0}' AND e.TourGroupID='{1}' AND (", WipeOffMethodEnum.签单报账, tourGroupId);

//            formatSql.AppendFormat(@"SELECT en.[EntertainmentOrderID],en.[ExpenseID], en.[TourgroupID],en.[TourgroupName],en.[ProviderID],en.[ProviderName], 
//                                                en.[AdultNum],en.[AdultUnitPrice],en.[AccompanyNum],en.[AccompanyUnitPrice],en.[LocalPlanScheduleID], en.[ServiceDate], 
//                                                en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], en.[AccompanyUnitPrice],
//                                                en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState], 
//                                                e.[Budget],e.[Status],[TypeBelong] ,[CreateByUserName],[TourGroupScheduleID]  
//                                                FROM fee_Expense e,fee_EntertainmentOrder en ,bas_provider p 
//                                                WHERE  e.expenseid=en.expenseid AND en.[ProviderID]=p.[ProviderID] AND p.IsSigned=1 AND e.TourGroupID='{0}' AND (", tourGroupId);
            for (int i = 0; i < types.Length; i++)
            {
                formatSql.AppendFormat(" e.TypeBelong='{0}' ", types[i]);

                if (i != types.Length - 1)
                    formatSql.Append(" OR ");
            }
            formatSql.Append(")");

            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 依据大行程计划号娱乐餐单预定数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetEntertainmentOrderData(int tourGroupScheduleId,string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘娱乐餐单预定信息’表联合数据
            string sql = string.Format(@"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID], en.[TourgroupName],
                                                en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
                                                en.[AccompanyUnitPrice], en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],e.[WipeOffMethod], 
                                                e.[TypeBelong],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[ServiceName],e.[PayTotal],e.[Remark] as ExpenseRemark  
                                                FROM [fee_EntertainmentOrder] en,fee_Expense e WHERE  en.ExpenseID=e.ExpenseID AND e.TourGroupScheduleID={0} AND en.ExpenseID={1}", tourGroupScheduleId, expenseId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 依据大行程计划号娱乐餐单预定数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
//        internal static DataTable GetEntertainmentOrderData(int tourGroupScheduleId)
//        {

//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;

//            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
//            string sql = string.Format(@"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID], en.[TourgroupName],
//                                                en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
//                                                en.[AccompanyUnitPrice], en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],e.[WipeOffMethod], 
//                                                e.[TypeBelong],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[ServiceName],e.[PayTotal] 
//                                                FROM [fee_EntertainmentOrder] en,fee_Expense e WHERE  en.ExpenseID=e.ExpenseID AND e.TourGroupScheduleID={0}", tourGroupScheduleId);

//            dbCommand = db.GetSqlStringCommand(sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0)
//                return ds.Tables[0];
//            else
//                return null;
//        }
        /// <summary>
        /// 根据团号及本地行程计划号获取行程预订信息
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="localPlanScheduleId"></param>
        /// <returns></returns>
        internal static DataTable GetEntertainmentOrderData(string tourGroupId, string localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            StringBuilder formatSql = new StringBuilder();

            //根据供应商为签约供应商，和制定团号的签单娱乐信息
            formatSql.AppendFormat(@"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID], en.[TourgroupName],
                                        en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
                                        en.[AccompanyUnitPrice], en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],e.[WipeOffMethod], 
                                        e.[Budget],e.[Status],e.[TypeBelong],e.[PayStatus]
                                        FROM fee_Expense e,fee_EntertainmentOrder en WHERE  en.TourGroupID='{0}' AND e.expenseid=en.expenseid AND en.LocalPlanScheduleID={1} AND (e.TypeBelong='{2}' OR e.TypeBelong='{3}') ",
                                        tourGroupId, localPlanScheduleId, ProviderTypeEnum.演出, ProviderTypeEnum.景点);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetRestaurantOrderData(string tourGroupId, string localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            StringBuilder formatSql = new StringBuilder();

            //根据供应商为签约供应商，和制定团号的签单娱乐信息
            formatSql.AppendFormat(@"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID], en.[TourgroupName],
                                        en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
                                        en.[AccompanyUnitPrice], en.[OthersCost], en.[CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],e.[WipeOffMethod], 
                                        e.[Budget],e.[Status],e.[TypeBelong],e.[PayStatus]
                                        FROM fee_Expense e,fee_EntertainmentOrder en WHERE  en.TourGroupID='{0}' AND e.expenseid=en.expenseid AND en.LocalPlanScheduleID={1} AND (e.TypeBelong='{2}') ORDER BY en.[ServiceName] DESC",
                                        tourGroupId, localPlanScheduleId, ProviderTypeEnum.餐厅);

            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 娱乐、餐单预订信息的增、删、改

        internal static int CreateEntertainmenRestaurantOrderAndExpenseData(ExpenseEntity expense, EntertainmenRestaurantOrderEntity entertaintOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType],[WipeOffMethod])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}','{18}');Select SCOPE_IDENTITY();",
                                        expense.TourgroupID,
                                        expense.ProviderID,
                                        expense.ProviderName,
                                        expense.ServiceName,
                                        expense.Budget,
                                        expense.PayTotal,
                                        expense.ApplyPayTotal,
                                        expense.AdjustAmount,
                                        expense.Status,
                                        expense.TypeBelong,
                                        expense.ApplyPay,
                                        expense.PayStatus,
                                        expense.TourgroupName,
                                        expense.CreateByUserName,
                                        expense.TourGroupScheduleID,
                                        expense.CreateTime,
                                        expense.ExpenseTypeID,
                                        expense.ExpenseType,
                                        expense.WipeOffMethod);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlOrder = string.Format(@"INSERT INTO fee_EntertainmentOrder([ExpenseID], [TourgroupID], [TourgroupName], [ProviderID], [ProviderName], 
                                                                                  [LocalPlanScheduleID], [ServiceDate], [AccompanyNum], [AdultNum], [ChildNum], [AdultUnitPrice],
                                                                                  [ChildUnitPrice], [AccompanyUnitPrice], [OthersCost], [CiceroniName], [CiceroniPhone], 
                                                                                  [ServiceName], [ContractPath], [Remark], [OrderState])
                                                                                  VALUES({0},'{1}','{2}',{3},'{4}',{5},'{6}',{7},{8},{9},{10},{11},{12},{13},'{14}','{15}', '{16}','{17}','{18}','{19}');Select SCOPE_IDENTITY();",
                                                                                   expenseID,
                                                                                   expense.TourgroupID,
                                                                                   entertaintOrder.TourgroupName,
                                                                                   entertaintOrder.ProviderID,
                                                                                   entertaintOrder.ProviderName,
                                                                                   entertaintOrder.LocalPlanScheduleID,
                                                                                   entertaintOrder.ServiceDate,
                                                                                   entertaintOrder.AccompanyNum,
                                                                                   entertaintOrder.AdultNum,
                                                                                   entertaintOrder.ChildNum,
                                                                                   entertaintOrder.AdultUnitPrice,
                                                                                   entertaintOrder.ChildUnitPrice,
                                                                                   entertaintOrder.AccompanyUnitPrice,
                                                                                   entertaintOrder.OthersCost,
                                                                                   entertaintOrder.CiceroniName,
                                                                                   entertaintOrder.CiceroniPhone,
                                                                                   entertaintOrder.ServiceName,
                                                                                   entertaintOrder.ContractPath,
                                                                                   entertaintOrder.Remark,
                                                                                   entertaintOrder.OrderState);
            int orderid = int.Parse(db.ExecuteScalar(CommandType.Text, sqlOrder).ToString());
            return orderid;
        }

        /// <summary>
        /// 计调更新娱乐、餐单预订的部分数据信息，不包括(EntertainmentOrderID ExpenseID TourgroupID TourgroupName ProviderID ProviderName LocalPlanScheduleID ServiceName ContractPath OrderState)字段
        /// </summary>                                                                                                             
        /// <param name="expense"></param>                                                                                        
        /// <param name="flightOrder"></param>                                                                                     
        /// <returns></returns>                                                                                                    
        internal static int UpdateEntertainmenRestaurantOrderData(ExpenseEntity expense, EntertainmenRestaurantOrderEntity entertainmentRestaurantOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0},
                                        [ProviderName]='{1}',
                                        [ServiceName]='{2}',
                                        [Budget]={3},
                                        [ExpenseTypeID]={4},
                                        [ExpenseType]='{5}',
                                        [TypeBelong]='{6}'
                                        WHERE ExpenseID={7}",
                                       expense.ProviderID,
                                       expense.ProviderName,
                                       expense.ServiceName,
                                       expense.Budget,
                                       expense.ExpenseTypeID,
                                       expense.ExpenseType,
                                       expense.TypeBelong,
                                       expense.ExpenseID);

            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlOrder = string.Format(@"UPDATE fee_EntertainmentOrder SET [ServiceDate]='{0}', [AccompanyNum]={1}, [AdultNum]={2}, [ChildNum]={3}, [AdultUnitPrice]={4}, 
                                                                                [ChildUnitPrice]={5}, [AccompanyUnitPrice]={6}, [OthersCost]={7}, [CiceroniName]='{8}', 
                                                                                [CiceroniPhone]='{9}',[Remark]='{10}',[ProviderID]={11}, [ProviderName]='{12}',[ServiceName]='{13}'   
                                                                                WHERE EntertainmentOrderID={14}",//EntertainmentOrderID ExpenseID TourgroupID TourgroupName LocalPlanScheduleID ServiceName ContractPath OrderState
                                                                                entertainmentRestaurantOrder.ServiceDate,
                                                                                entertainmentRestaurantOrder.AccompanyNum,
                                                                                entertainmentRestaurantOrder.AdultNum,
                                                                                entertainmentRestaurantOrder.ChildNum,
                                                                                entertainmentRestaurantOrder.AdultUnitPrice,
                                                                                entertainmentRestaurantOrder.ChildUnitPrice,
                                                                                entertainmentRestaurantOrder.AccompanyUnitPrice,
                                                                                entertainmentRestaurantOrder.OthersCost,
                                                                                entertainmentRestaurantOrder.CiceroniName,
                                                                                entertainmentRestaurantOrder.CiceroniPhone,
                                                                                entertainmentRestaurantOrder.Remark,
                                                                                entertainmentRestaurantOrder.ProviderID,
                                                                                entertainmentRestaurantOrder.ProviderName,
                                                                                entertainmentRestaurantOrder.ServiceName,
                                                                                entertainmentRestaurantOrder.EntertainmentOrderID
                                                                                );

            return db.ExecuteNonQuery(CommandType.Text, sqlOrder);
        }
        /// <summary>
        /// 计调更新娱乐、餐单预订的部分数据信息，仅服务项目和备注字段
        /// </summary>                                                                                                             
        /// <param name="expense"></param>                                                                                        
        /// <param name="flightOrder"></param>                                                                                     
        /// <returns></returns>                                                                                                    
        internal static int UpdateEntertainmenRestaurantOrderPart( EntertainmenRestaurantOrderEntity entertainmentRestaurantOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlOrder = string.Format(@"UPDATE fee_EntertainmentOrder SET [ServiceName]='{0}', [Remark]='{1}'
                                                                                WHERE EntertainmentOrderID={2}",//EntertainmentOrderID ExpenseID TourgroupID TourgroupName LocalPlanScheduleID ServiceName ContractPath OrderState
                                                                                entertainmentRestaurantOrder.ServiceName,
                                                                                entertainmentRestaurantOrder.Remark,
                                                                                entertainmentRestaurantOrder.EntertainmentOrderID
                                                                                );

            return db.ExecuteNonQuery(CommandType.Text, sqlOrder);
        }
        /// <summary>
        /// 更改娱乐、餐单的订单状态
        /// </summary>
        /// <param name="trainOrderId">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        //internal static int UpdateEntertainmenRestauranOrderStateData(string entertainmentOrderID, OrderStateEnum orderState)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"UPDATE fee_EntertainmentOrder SET [OrderState]='{0}' WHERE EntertainmentOrderID={1}", orderState, entertainmentOrderID);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        /// <summary>
        /// 更改娱乐、餐单的行程ID，因删除本地行程
        /// </summary>
        /// <param name="fromLocalPlanScheduleId">原行程ID</param>
        /// <param name="toLocalPlanScheduleId">新行程ID</param>
        /// <returns></returns>
        //internal static int UpdateEntertainmenRestauranLocalScheduleID(string fromLocalPlanScheduleId, string toLocalPlanScheduleId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"UPDATE fee_EntertainmentOrder SET [LocalPlanScheduleID]='{0}' WHERE LocalPlanScheduleID={1}", toLocalPlanScheduleId, fromLocalPlanScheduleId);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        /// <summary>
        /// 删除指定EntertainmentOrderID和费用标识ExpenseID的娱乐、餐单预订及费用信息
        /// </summary>
        /// <param name="entertainmentOrderId"></param>
        /// <param name="expenseId"></param>
        /// <returns></returns>
//        internal static int DeleteEntertainmenRestaurantOrder(string entertainmentOrderId,string expenseId)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string sql = string.Format(@"DELETE FROM fee_EntertainmentOrder WHERE EntertainmentOrderID={0};
//                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
//                                         entertainmentOrderId, expenseId);
//            return db.ExecuteNonQuery(CommandType.Text, sql);
//        }
        
        /// <summary>
        ///  删除指定本地行程计划号的娱乐、餐单预订及费用信息
        /// </summary>
        /// <param name="localPlanScheduleId"></param>
        /// <returns></returns>
//        internal static void DeleteEntertainmenRestaurantOrder(string localPlanScheduleId)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string sql = string.Format("select expenseid from fee_EntertainmentOrder where LocalPlanScheduleID={0}", localPlanScheduleId);
//            DbCommand dbCmd = db.GetSqlStringCommand(sql);

//            using (IDataReader reader = db.ExecuteReader(dbCmd))
//            {
//                while (reader.Read())
//                    db.ExecuteNonQuery(CommandType.Text,
//                                       string.Format(@"DELETE FROM fee_EntertainmentOrder WHERE LocalPlanScheduleID={0};
//                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
//                                             localPlanScheduleId,
//                                             reader.GetInt32(0)));
//            }
//            //return db.ExecuteNonQuery(CommandType.Text, sql);
//        }


        #endregion


        #region 娱乐、餐单计调信息查询

        internal static DataTable GetEntertainmentRestaurantOrderData(string tourGroupId, string providerId, DateTime startDate, DateTime endDate, string entertainmentOrderId,OrderStateEnum orderState)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘火车预定信息’表联合数据

            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "en.TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND e.ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " e.ProviderID='" + providerId + "'";
            }
            if (szCondition != "")
            {
                if (entertainmentOrderId != "00000000")
                    szCondition += " AND en.EntertainmentOrderID='" + int.Parse(entertainmentOrderId) + "'";
            }
            else
            {
                if (entertainmentOrderId != "00000000")
                    szCondition += " en.EntertainmentOrderID='" + int.Parse(entertainmentOrderId) + "'";
            }
            if (szCondition != "")
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" AND en.ServiceDate>='{0}' AND en.ServiceDate<='{1}' AND en.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" AND en.ServiceDate>='{0}' AND en.ServiceDate<='{1}' AND en.OrderState='{2}' AND en.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }
            else
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" en.ServiceDate>='{0}' AND en.ServiceDate<='{1}' AND en.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" en.ServiceDate>='{0}' AND en.ServiceDate<='{1}' AND en.OrderState='{2}' AND en.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }



            string Sql = @"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID],en.[TourgroupName],
                                        en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
                                        en.[AccompanyUnitPrice], en.[OthersCost], [CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],
                                        e.[ProviderID], e.[ProviderName],e.[Budget],e.[Status],e.[TypeBelong],e.[PayTotal],e.[ApplyPayTotal],e.[Budget]-e.[ApplyPayTotal] as ApplyPay,e.[PayStatus],e.[ExpenseTypeID],e.[ExpenseType]   
                                        FROM fee_Expense e,fee_EntertainmentOrder en  ";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetEntertainmentRestaurantOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT en.[EntertainmentOrderID], en.[ExpenseID], en.[ProviderID], en.[ProviderName], en.[TourgroupID],en.[TourgroupName],
                                        en.[LocalPlanScheduleID], en.[ServiceDate], en.[AccompanyNum], en.[AdultNum], en.[ChildNum], en.[AdultUnitPrice], en.[ChildUnitPrice], 
                                        en.[AccompanyUnitPrice], en.[OthersCost], [CiceroniName], en.[CiceroniPhone], en.[ServiceName], en.[ContractPath], en.[Remark],en.[OrderState],
                                        e.[ProviderID], e.[ProviderName],e.[Budget],e.[Status],e.[TypeBelong]  
                                        FROM fee_Expense e,fee_EntertainmentOrder en WHERE e.ExpenseID=en.ExpenseID AND e.ExpenseID={0} ", expenseId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        public static DataTable GetEntertainmenRestaurantOrderData(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //string sql = string.Format(@"SELECT r.*,p.City FROM fee_EntertainmentOrder r,bas_Provider p WHERE r.ProviderID=p.ProviderID AND TourgroupID='{0}'", tourgroupId);
            string sql = string.Format(@"SELECT r.*,p.City FROM fee_EntertainmentOrder r,bas_Provider p WHERE r.ProviderID=p.ProviderID AND TourgroupID='{0}'  AND p.ProviderType='{1}'", tourgroupId,ProviderTypeEnum.餐厅);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
    }
}
